﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
//using Com.LongER.Business.ZDH004.ZDH004DSTableAdapters;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Com.LongER.Framework.Utils.LogUtils;

namespace Com.LongER.Business.ZDH004
{
    public class CZDH004 : ABussinessBase
    {
        private Log log = Log.GetInstance();
        /// <summary>
        /// 
        /// </summary>
        /// <param name="htParam"></param>
        /// <returns></returns>
        public ZDH004DS.T_CHESTADataTable GetCheStaList(Dictionary<string, string> dicParam)
        {
            string strCHEPAI_NUM = dicParam["CHEPAI_NUM"];
            string strSTA_DAY = dicParam["STA_DAY"];
            string strSTS_HOUR = dicParam["STS_HOUR"];
            string strEND_DAY = dicParam["END_DAY"];
            string strEND_HOUR = dicParam["END_HOUR"];

            string strBETWEEN_START = ((int.Parse(dicParam["PAGE_NO"]) - 1) * 5 + 1).ToString();
            string strBETWEEN_END = (int.Parse(dicParam["PAGE_NO"]) * 5).ToString();

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     *                                                                           ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     (                                                                           ");
            objSqlContent.Append("         SELECT                                                                  ");
            objSqlContent.Append("             C.CHEPAI_NUM    AS CHEPAI_NUM                                       ");
            objSqlContent.Append("             ,ISNULL(C.JL_DATE,'') AS JL_DATE                                    ");
            objSqlContent.Append("             ,ISNULL(C.JL_TIME,'') AS JL_TIME                                    ");
            objSqlContent.Append("             ,ISNULL(C.YUNYING_STA,'') AS YUNYING_STA                            ");
            objSqlContent.Append("             ,ISNULL(YYZT.CODE1_HANZI,'') AS YYZT_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.YUNSHU_STA,'') AS YUNSHU_STA                              ");
            objSqlContent.Append("             ,ISNULL(YSZT.CODE1_HANZI,'') AS YSZT_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.ZHUANGZAI_STA,'')  AS ZHUANGZAI_STA                       ");
            objSqlContent.Append("             ,ISNULL(ZZZT.CODE1_HANZI,'') AS ZZZT_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.ZHUANGZAI_LV,0)  AS  ZHUANGZAI_LV                         ");
            objSqlContent.Append("             ,ISNULL(C.SUOZAI_SHENGSHI, '') AS  SUOZAI_SHENGSHI                  ");
            objSqlContent.Append("             ,ISNULL(SENS.CODE1_HANZI, '') AS  SENS_HANZI                        ");
            objSqlContent.Append("             ,ISNULL(C.SUOZAI_LUDUAN, '') AS SUOZAI_LUDUAN                       ");
            objSqlContent.Append("             ,ISNULL(C.DETAIL_LUDUAN,'') AS DETAIL_LUDUAN                        ");
            objSqlContent.Append("             ,ISNULL(C.BEIZHU,'')  AS BEIZHU                                     ");
            objSqlContent.Append("             ,rn = ROW_NUMBER                                                    ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("             OVER                                                                ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             ORDER BY                                                            ");
            objSqlContent.Append("                 C.CHEPAI_NUM ASC                                                ");
            objSqlContent.Append("                ,C.JL_DATE + C.JL_TIME ASC                                       ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("         FROM                                                                    ");
            objSqlContent.Append("             T_CHE_STATUS C                                                      ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG YYZT                                                     ");
            objSqlContent.Append("             ON  C.YUNYING_STA = YYZT.KEY1                                       ");
            objSqlContent.Append("             AND YYZT.CODE_CD = 'YYZT'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG YSZT                                                     ");
            objSqlContent.Append("             ON  C.YUNSHU_STA = YSZT.KEY1                                        ");
            objSqlContent.Append("             AND YSZT.CODE_CD = 'YSZT'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG ZZZT                                                     ");
            objSqlContent.Append("             ON  C.ZHUANGZAI_STA = ZZZT.KEY1                                     ");
            objSqlContent.Append("             AND ZZZT.CODE_CD = 'ZZZT'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG SENS                                                     ");
            objSqlContent.Append("             ON  C.SUOZAI_SHENGSHI = SENS.KEY1                                   ");
            objSqlContent.Append("             AND SENS.CODE_CD = 'SENS'                                           ");
            objSqlContent.Append("         WHERE                                                                   ");
            objSqlContent.Append("                 1 = 1                                                           ");

            //日期时间作为条件时，有问题的
            if (!String.IsNullOrWhiteSpace(strCHEPAI_NUM))
                objSqlContent.Append("             AND C.CHEPAI_NUM = @CHEPAI_NUM                                  ");
            if (!String.IsNullOrWhiteSpace(strSTA_DAY) && !String.IsNullOrWhiteSpace(strSTS_HOUR))
                objSqlContent.Append("             AND C.JL_DATE + C.JL_TIME >= @STA_DAY  +  @STS_HOUR           ");
            if (!String.IsNullOrWhiteSpace(strEND_DAY) && !String.IsNullOrWhiteSpace(strEND_HOUR))
                objSqlContent.Append("             AND C.JL_DATE + C.JL_TIME <= @END_DAY  +  @END_HOUR           ");
            objSqlContent.Append("     )                                                                           ");
            objSqlContent.Append("     A                                                                           ");
            objSqlContent.Append(" WHERE                                                                           ");
            objSqlContent.Append("         A.rn BETWEEN @BETWEEN_START AND @BETWEEN_END;                           ");
            #endregion

            cnnstr = objSqlContent.ToString();

            log.Debug("CZDH004:" + cnnstr);

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            //车牌号
            if (!String.IsNullOrWhiteSpace(strCHEPAI_NUM))
                cmd.Parameters.Add(getSqlParameter("CHEPAI_NUM", SqlDbType.Char, strCHEPAI_NUM));
            //起始日时_Day
            if (!String.IsNullOrWhiteSpace(strSTA_DAY))
                cmd.Parameters.Add(getSqlParameter("STA_DAY", SqlDbType.Char, strSTA_DAY));
            //起始日时_Hour
            if (!String.IsNullOrWhiteSpace(strSTS_HOUR))
                cmd.Parameters.Add(getSqlParameter("STS_HOUR", SqlDbType.Char, strSTS_HOUR));

            //终了日时_Day
            if (!String.IsNullOrWhiteSpace(strEND_DAY))
                cmd.Parameters.Add(getSqlParameter("END_DAY", SqlDbType.Char, strEND_DAY));
            //终了日时_Hour
            if (!String.IsNullOrWhiteSpace(strEND_HOUR))
                cmd.Parameters.Add(getSqlParameter("END_HOUR", SqlDbType.Char, strEND_HOUR));

            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_START))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_START", SqlDbType.Int, int.Parse(strBETWEEN_START)));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_END))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_END", SqlDbType.Int, int.Parse(strBETWEEN_END)));

            SqlDataReader odr = cmd.ExecuteReader();

            ZDH004DS.T_CHESTADataTable tb = new ZDH004DS.T_CHESTADataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZDH004DS.T_CHESTARow dr = tb.NewT_CHESTARow();
                    //row 1
                    dr.CHEPAI_NUM = odr["CHEPAI_NUM"].ToString();
                    dr.JL_DATE = odr["JL_DATE"].ToString();//车辆行驶日时
                    dr.JL_TIME = odr["JL_TIME"].ToString();//车辆行驶日时
                    dr.SUOZAI_SHENGSHI = odr["SUOZAI_SHENGSHI"].ToString();  //所在省市
                    dr.SENS_HANZI = odr["SENS_HANZI"].ToString();  //所在省市汉字
                    dr.SUOZAI_LUDUAN = odr["SUOZAI_LUDUAN"].ToString();//路段名
                    dr.DETAIL_LUDUAN = odr["DETAIL_LUDUAN"].ToString();//详细路段
                    dr.YUNYING_STA = odr["YUNYING_STA"].ToString();//运营状态
                    dr.YYZT_HANZI = odr["YYZT_HANZI"].ToString();//运营状态汉字
                    dr.YUNSHU_STA = odr["YUNSHU_STA"].ToString();//运输状态
                    dr.YSZT_HANZI = odr["YSZT_HANZI"].ToString();//运输状态
                    dr.ZHUANGZAI_STA = odr["ZHUANGZAI_STA"].ToString();//装载状态
                    dr.ZZZT_HANZI = odr["ZZZT_HANZI"].ToString();//装载状态
                    dr.ZHUANGZAI_LV = odr["ZHUANGZAI_LV"].ToString(); //装载率

                    tb.AddT_CHESTARow(dr);
                }
            }

            con.Close();
            return tb;
        }


    }
}